package com.tianqu.tms.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.tianqu.tms.core.exception.TmsDaoException;
import com.tianqu.tms.core.util.StringUtil;
import com.tianqu.tms.dto.MemberDto;
import com.tianqu.tms.dto.TmsFinanciaDto;
import com.tianqu.tms.dto.TmsFinanciaParam;
import com.tianqu.tms.dto.TransferPersonalCarInfoDto;

public class TmsFinanciaDaoImp extends ViewListDao implements TmsFinanciaDao {
	protected final String[] sortKeyList = new String[] {"financiaId","financiaName","contactOwner","contactPhone","legal","phone","insertTime","phone"};// 列表

	protected TmsFinanciaDaoImp(int pageNo, int maxIndex,int sortKey,boolean asc) throws TmsDaoException{
		super( pageNo,maxIndex, sortKey, asc);
	}
	
	 
	protected TmsFinanciaDaoImp() throws TmsDaoException {
		super();
	}

	@Override
	public void insert(TmsFinanciaDto dto) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			StringBuffer sql = new StringBuffer("INSERT INTO  ");
			sql.append(TmsTableNames.TMS_FINANCIA);
			sql.append(" (  ");
			sql.append(" FINANCIAID,  ");
			sql.append(" FINANCIANAME,  ");
			sql.append(" SHORTNAME,  ");
			sql.append(" REALNAMEPY,  ");
			sql.append(" CONTACTOWNER,  ");
			sql.append(" CONTACTPHONE,  ");
			sql.append(" LEGAL,  ");
			sql.append(" PHONE,  ");
			sql.append(" REGISTEREDCAPITAL,  ");
			sql.append(" CARDNO,  ");
			sql.append(" SETTLEPHONE,  ");
			sql.append(" ORGNO,  ");
			sql.append(" CARDPHOTO,  ");
			sql.append(" ORGPHOTO,  ");
			sql.append(" BUSINESSPHOTO,  ");
			sql.append(" MEMBERZIPCODE,  ");
			sql.append(" MEMBERLOGO,  ");
			sql.append(" CARPORTINSTR,  ");
			sql.append(" USERID,  ");
			sql.append(" UPDATEUSERID,  ");
			sql.append(" INSERTUSERID,  ");
			sql.append(" UPDATETIME,  ");
			sql.append(" INSERTTIME  ");
			sql.append(" )  ");
			sql.append(" VALUES  ");
			sql.append(" (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,SYSDATE(),SYSDATE() ) ");
			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, dto.getFinanciaId());
			ps.setString(2, dto.getFinanciaName());
			ps.setString(3, dto.getShortName());
			ps.setString(4, dto.getRealNamePy());
			ps.setString(5, dto.getContactOwner());
			ps.setString(6, dto.getContactPhone());
			ps.setString(7, dto.getLegal());
			ps.setString(8, dto.getPhone());
			ps.setInt(9, dto.getRegisteredCapital());
			ps.setString(10, dto.getCardNo());
			ps.setString(11, dto.getBirthday());
			ps.setString(12, dto.getOrgNo());
			ps.setString(13, dto.getCardPhoto());
			ps.setString(14, dto.getOrgPhoto());
			ps.setString(15, dto.getBusinessPhoto());
			ps.setString(16, dto.getMemberZipCode());
			ps.setString(17, dto.getMemberLogo());
			ps.setString(18, dto.getCarPortInstr());
			ps.setString(19, dto.getUserId());
			ps.setInt(20, dto.getUpdateUserId());
			ps.setInt(21, dto.getInsertUserId());
			ps.executeUpdate();

		} catch (SQLException e) {
			logger.error("TmsFinanciaDaoImp.insert error", e);
			throw new TmsDaoException(e.toString());
		} finally {
			super.close(rs, ps);
		}
	}

	@Override
	public void update(TmsFinanciaDto dto) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		int i = 0;
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE ");
		sql.append(TmsTableNames.TMS_FINANCIA);
		sql.append(" SET ");
		sql.append(" FINANCIAID=").append(StringUtil.isNotBlank(dto.getFinanciaId()) ? "?" : "FINANCIAID").append(",");
		sql.append(" FINANCIANAME=").append(StringUtil.isNotBlank(dto.getFinanciaName()) ? "?" : "FINANCIANAME") .append(",");
		sql.append(" SHORTNAME=").append(StringUtil.isNotBlank(dto.getShortName()) ? "?" : "SHORTNAME").append(",");
		sql.append(" REALNAMEPY=").append(StringUtil.isNotBlank(dto.getRealNamePy()) ? "?" : "REALNAMEPY").append(",");
		sql.append(" CONTACTOWNER=").append(StringUtil.isNotBlank(dto.getContactOwner()) ? "?" : "CONTACTOWNER") .append(",");
		sql.append(" CONTACTPHONE=").append(StringUtil.isNotBlank(dto.getContactPhone()) ? "?" : "CONTACTPHONE").append(",");
		sql.append(" LEGAL=").append(StringUtil.isNotBlank(dto.getLegal()) ? "?" : "LEGAL").append(",");
		sql.append(" PHONE=").append(StringUtil.isNotBlank(dto.getPhone()) ? "?" : "PHONE").append(",");
		sql.append(" REGISTEREDCAPITAL=").append(dto.getRegisteredCapital() > 0 ? "?" : "REGISTEREDCAPITAL").append(",");
		sql.append(" CARDNO=").append(StringUtil.isNotBlank(dto.getCardNo()) ? "?" : "CARDNO").append(",");
		sql.append(" SETTLEPHONE=").append(StringUtil.isNotBlank(dto.getBirthday()) ? "?" : "SETTLEPHONE").append(",");
		sql.append(" ORGNO=").append(StringUtil.isNotBlank(dto.getOrgNo()) ? "?" : "ORGNO").append(",");
		sql.append(" CARDPHOTO=").append(StringUtil.isNotBlank(dto.getCardPhoto()) ? "?" : "CARDPHOTO").append(",");
		sql.append(" ORGPHOTO=").append(StringUtil.isNotBlank(dto.getOrgPhoto()) ? "?" : "ORGPHOTO").append(",");
		sql.append(" BUSINESSPHOTO=").append(StringUtil.isNotBlank(dto.getBusinessPhoto()) ? "?" : "BUSINESSPHOTO").append(",");
		sql.append(" MEMBERZIPCODE=").append(StringUtil.isNotBlank(dto.getMemberZipCode()) ? "?" : "MEMBERZIPCODE").append(",");
		sql.append(" MEMBERLOGO=").append(StringUtil.isNotBlank(dto.getMemberLogo()) ? "?" : "MEMBERLOGO").append(",");
		sql.append(" CARPORTINSTR=").append(StringUtil.isNotBlank(dto.getCarPortInstr()) ? "?" : "CARPORTINSTR").append(",");
		sql.append(" USERID=").append(StringUtil.isNotBlank(dto.getUserId()) ? "?" : "USERID").append(",");
		sql.append(" UPDATEUSERID=").append(dto.getUpdateUserId() > 0 ? "?" : "UPDATEUSERID").append(",");
		sql.append(" UPDATETIME=SYSDATE()");
		sql.append(" WHERE ");
		sql.append(" ID=? ");
		try {
			ps = conn.prepareStatement(sql.toString());
			if (StringUtil.isNotBlank(dto.getFinanciaId()))
				ps.setString(++i, dto.getFinanciaId());
			if (StringUtil.isNotBlank(dto.getFinanciaName()))
				ps.setString(++i, dto.getFinanciaName());
			if (StringUtil.isNotBlank(dto.getShortName()))
				ps.setString(++i, dto.getShortName());
			if (StringUtil.isNotBlank(dto.getRealNamePy()))
				ps.setString(++i, dto.getRealNamePy());
			if (StringUtil.isNotBlank(dto.getContactOwner()))
				ps.setString(++i, dto.getContactOwner());
			if (StringUtil.isNotBlank(dto.getContactPhone()))
				ps.setString(++i, dto.getContactPhone());
			if (StringUtil.isNotBlank(dto.getLegal()))
				ps.setString(++i, dto.getLegal());
			if (StringUtil.isNotBlank(dto.getPhone()))
				ps.setString(++i, dto.getPhone());
			if (dto.getRegisteredCapital() > 0)
				ps.setInt(++i, dto.getRegisteredCapital());
			if (StringUtil.isNotBlank(dto.getCardNo()))
				ps.setString(++i, dto.getCardNo());
			if (StringUtil.isNotBlank(dto.getBirthday()))
				ps.setString(++i, dto.getBirthday());
			if (StringUtil.isNotBlank(dto.getOrgNo()))
				ps.setString(++i, dto.getOrgNo());
			if (StringUtil.isNotBlank(dto.getCardPhoto()))
				ps.setString(++i, dto.getCardPhoto());
			if (StringUtil.isNotBlank(dto.getOrgPhoto()))
				ps.setString(++i, dto.getOrgPhoto());
			if (StringUtil.isNotBlank(dto.getBusinessPhoto()))
				ps.setString(++i, dto.getBusinessPhoto());
			if (StringUtil.isNotBlank(dto.getMemberZipCode()))
				ps.setString(++i, dto.getMemberZipCode());
			if (StringUtil.isNotBlank(dto.getMemberLogo()))
				ps.setString(++i, dto.getMemberLogo());
			if (StringUtil.isNotBlank(dto.getCarPortInstr()))
				ps.setString(++i, dto.getCarPortInstr());
			if (StringUtil.isNotBlank(dto.getUserId()))
				ps.setString(++i, dto.getUserId());
			if (dto.getUpdateUserId() > 0)
				ps.setInt(++i, dto.getUpdateUserId());
			ps.setLong(++i, dto.getId());
			ps.executeUpdate();
		} catch (SQLException e) {
			logger.error("TmsFinanciaDaoImp.update error", e);
			throw new TmsDaoException(e.toString());
		} finally {
			super.close(rs, ps);
		}
	}

	public TmsFinanciaDto get(String id) throws TmsDaoException{
		PreparedStatement ps = null;
		ResultSet rs = null;
		TmsFinanciaDto dto = new TmsFinanciaDto();
		try {
			StringBuffer sql = new StringBuffer("");
			sql.append("SELECT ");
			sql.append(" FINANCIA.ID,  ");
			sql.append(" FINANCIA.FINANCIAID,  ");
			sql.append(" FINANCIA.FINANCIANAME,  ");
			sql.append(" FINANCIA.SHORTNAME,  ");
			sql.append(" FINANCIA.REALNAMEPY,  ");
			sql.append(" FINANCIA.CONTACTOWNER,  ");
			sql.append(" FINANCIA.CONTACTPHONE,  ");
			sql.append(" FINANCIA.LEGAL,  ");
			sql.append(" FINANCIA.PHONE,  ");
			sql.append(" FINANCIA.REGISTEREDCAPITAL,  ");
			sql.append(" FINANCIA.CARDNO,  ");
			sql.append(" FINANCIA.SETTLEPHONE,  ");
			sql.append(" FINANCIA.ORGNO,  ");
			sql.append(" FINANCIA.CARDPHOTO,  ");
			sql.append(" FINANCIA.ORGPHOTO,  ");
			sql.append(" FINANCIA.BUSINESSPHOTO,  ");
			sql.append(" FINANCIA.MEMBERZIPCODE,  ");
			sql.append(" FINANCIA.MEMBERLOGO,  ");
			sql.append(" FINANCIA.CARPORTINSTR,  ");
			sql.append(" FINANCIA.USERID,  ");
			sql.append(" FINANCIA.UPDATEUSERID,  ");
			sql.append(" FINANCIA.INSERTUSERID,  ");
			sql.append(" FINANCIA.UPDATETIME,  ");
			sql.append(" FINANCIA.INSERTTIME,  ");
			sql.append(" TMS_USER.PASSWORD,  ");
			sql.append(" TMS_USER.FLAG as STATUS  ");// 与user表关联取出帐号状态
			sql.append("FROM ");
			sql.append(TmsTableNames.TMS_FINANCIA);
			sql.append(" AS FINANCIA ");
			sql.append(" LEFT JOIN ");
			sql.append(TmsTableNames.TMS_USER);
			sql.append(" AS TMS_USER ");
			sql.append(" ON TMS_USER.USERID = FINANCIA.USERID ");
			sql.append(" WHERE 1=1");
			sql.append(" AND FINANCIA.ID = '" + id + "'");
			// 排序方法
			sql.append(getOrderBySql(sortKeyList));
			ps = conn.prepareStatement(getSql(sql.toString()));
			rs = ps.executeQuery();

			while (rs.next()) {
				dto.setId(rs.getLong("ID"));
				dto.setStatus(rs.getString("STATUS"));
				dto.setFinanciaId(rs.getString("FINANCIAID"));
				dto.setFinanciaName(rs.getString("FINANCIANAME"));
				dto.setShortName(rs.getString("SHORTNAME"));
				dto.setRealNamePy(rs.getString("REALNAMEPY"));
				dto.setContactOwner(rs.getString("CONTACTOWNER"));
				dto.setContactPhone(rs.getString("CONTACTPHONE"));
				dto.setLegal(rs.getString("LEGAL"));
				dto.setPhone(rs.getString("PHONE"));
				dto.setRegisteredCapital(rs.getInt("REGISTEREDCAPITAL"));
				dto.setCardNo(rs.getString("CARDNO")) ;
				dto.setSettlePhone(rs.getString("SETTLEPHONE"));
				dto.setOrgNo(rs.getString("ORGNO"));
				dto.setCardPhoto(rs.getString("CARDPHOTO"));
				dto.setOrgPhoto(rs.getString("ORGPHOTO"));
				dto.setBusinessPhoto(rs.getString("BUSINESSPHOTO"));
				dto.setMemberZipCode(rs.getString("MEMBERZIPCODE"));
				dto.setMemberLogo(rs.getString("MEMBERLOGO"));
				dto.setCarPortInstr(rs.getString("CARPORTINSTR"));
				dto.setUserId(rs.getString("USERID"));
				dto.setUpdateUserId(rs.getInt("UPDATEUSERID"));
				dto.setInsertUserId(rs.getInt("INSERTUSERID"));
				dto.setUpdateTime(rs.getString("UPDATETIME"));
				dto.setInsertTime(rs.getString("INSERTTIME"));
				dto.setUserId(rs.getString("USERID"));
				dto.setPassword(rs.getString("PASSWORD"));
			}
		} catch (SQLException e) {
			logger.error("TmsFinanciaDaoImp.get error", e);
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}
		return dto;
	}
	
	@Override
	public List<TmsFinanciaDto> find(TmsFinanciaParam param) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<TmsFinanciaDto> list = new ArrayList<TmsFinanciaDto>();
		try {
			StringBuffer sql = new StringBuffer("");
			sql.append("SELECT ");
			sql.append(" FINANCIA.ID,  ");
			sql.append(" FINANCIA.FINANCIAID,  ");
			sql.append(" FINANCIA.FINANCIANAME,  ");
			sql.append(" FINANCIA.SHORTNAME,  ");
			sql.append(" FINANCIA.REALNAMEPY,  ");
			sql.append(" FINANCIA.CONTACTOWNER,  ");
			sql.append(" FINANCIA.CONTACTPHONE,  ");
			sql.append(" FINANCIA.LEGAL,  ");
			sql.append(" FINANCIA.PHONE,  ");
			sql.append(" FINANCIA.REGISTEREDCAPITAL,  ");
			sql.append(" FINANCIA.CARDNO,  ");
			sql.append(" FINANCIA.SETTLEPHONE,  ");
			sql.append(" FINANCIA.ORGNO,  ");
			sql.append(" FINANCIA.CARDPHOTO,  ");
			sql.append(" FINANCIA.ORGPHOTO,  ");
			sql.append(" FINANCIA.BUSINESSPHOTO,  ");
			sql.append(" FINANCIA.MEMBERZIPCODE,  ");
			sql.append(" FINANCIA.MEMBERLOGO,  ");
			sql.append(" FINANCIA.CARPORTINSTR,  ");
			sql.append(" FINANCIA.USERID,  ");
			sql.append(" FINANCIA.UPDATEUSERID,  ");
			sql.append(" FINANCIA.INSERTUSERID,  ");
			sql.append(" FINANCIA.UPDATETIME,  ");
			sql.append(" FINANCIA.INSERTTIME,  ");
			sql.append(" TMS_USER.PASSWORD,  ");
			sql.append(" TMS_USER.FLAG as STATUS  ");// 与user表关联取出帐号状态
			sql.append("FROM ");
			sql.append(TmsTableNames.TMS_FINANCIA);
			sql.append(" AS FINANCIA ");
			sql.append(" LEFT JOIN ");
			sql.append(TmsTableNames.TMS_USER);
			sql.append(" AS TMS_USER ");
			sql.append(" ON TMS_USER.USERID = FINANCIA.USERID ");
			sql.append(" WHERE 1=1");
			if (!StringUtil.isNullOrBlank(param.getId())) {
				sql.append(" AND FINANCIA.ID = '" + param.getId() + "'");
			}
			if (!StringUtil.isNullOrBlank(param.getFinanciaId())) {
				sql.append(" AND FINANCIA.FINANCIAID = '" + param.getFinanciaId() + "'");
			}
			if ("1".equals(param.getFlag())) {
				sql.append(" AND TMS_USER.FLAG = 1");
			} else if ("3".equals(param.getFlag())) {
				sql.append(" AND TMS_USER.FLAG = 3");
			}
			// 排序方法
			sql.append(getOrderBySql(sortKeyList));
			ps = conn.prepareStatement(getSql(sql.toString()));
			rs = ps.executeQuery();

			while (rs.next()) {
				TmsFinanciaDto dto = new TmsFinanciaDto();
				dto.setId(rs.getLong("ID"));
				dto.setStatus(rs.getString("STATUS"));
				dto.setFinanciaId(rs.getString("FINANCIAID"));
				dto.setFinanciaName(rs.getString("FINANCIANAME"));
				dto.setShortName(rs.getString("SHORTNAME"));
				dto.setRealNamePy(rs.getString("REALNAMEPY"));
				dto.setContactOwner(rs.getString("CONTACTOWNER"));
				dto.setContactPhone(rs.getString("CONTACTPHONE"));
				dto.setLegal(rs.getString("LEGAL"));
				dto.setPhone(rs.getString("PHONE"));
				dto.setRegisteredCapital(rs.getInt("REGISTEREDCAPITAL"));
				dto.setCardNo(rs.getString("CARDNO")) ;
				dto.setSettlePhone(rs.getString("SETTLEPHONE"));
				dto.setOrgNo(rs.getString("ORGNO"));
				dto.setCardPhoto(rs.getString("CARDPHOTO"));
				dto.setOrgPhoto(rs.getString("ORGPHOTO"));
				dto.setBusinessPhoto(rs.getString("BUSINESSPHOTO"));
				dto.setMemberZipCode(rs.getString("MEMBERZIPCODE"));
				dto.setMemberLogo(rs.getString("MEMBERLOGO"));
				dto.setCarPortInstr(rs.getString("CARPORTINSTR"));
				dto.setUserId(rs.getString("USERID"));
				dto.setUpdateUserId(rs.getInt("UPDATEUSERID"));
				dto.setInsertUserId(rs.getInt("INSERTUSERID"));
				dto.setUpdateTime(rs.getString("UPDATETIME"));
				dto.setInsertTime(rs.getString("INSERTTIME"));
				dto.setUserId(rs.getString("USERID"));
				dto.setPassword(rs.getString("PASSWORD"));
				list.add(dto);
			}
		} catch (SQLException e) {
			logger.error("TmsFinanciaDaoImp.find error", e);
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}
		return list;
	}

	@Override
	public boolean isRepeatName(String name, int type) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		boolean result = false;

		try {
			conn = super.getConnection();

			StringBuffer sql = new StringBuffer();
			sql.append("SELECT ");
			sql.append(" FINANCIA.FINANCIAID AS FINANCIAID ");
			sql.append(" FROM ");
			sql.append(TmsTableNames.TMS_FINANCIA);
			sql.append(" AS FINANCIA ");
			sql.append(" LEFT JOIN ");// 和user表关联
			sql.append(TmsTableNames.TMS_USER);
			sql.append(" AS USER ");
			sql.append(" ON USER.USERID=FINANCIA.USERID ");
			sql.append(" WHERE ");
			if (type == 1) {
				sql.append(" FINANCIA.FINANCIANAME=? ");
			} else if (type == 2) {
				sql.append(" FINANCIA.SHORTNAME=? ");
			}
			sql.append(" AND (USER.FLAG = 1 OR USER.FLAG = 3)");

			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, name);
			// 执行sql
			rs = ps.executeQuery();

			while (rs.next()) {
				result = true;
			}
			return result;
		} catch (SQLException e) {
			logger.error("TmsFinanciaDaoImp.isRepeatName error", e);
			throw new TmsDaoException(e.toString());

		} finally {
			close(rs, ps);
		}
	}

	@Override
	public List<TmsFinanciaDto> find(String realNamePy, String status, int limit) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<TmsFinanciaDto> list = new ArrayList<TmsFinanciaDto>();
		try {

			StringBuffer sql = new StringBuffer("");
			sql.append("SELECT ");
			sql.append(" FINANCIA.FINANCIAID,  ");
			sql.append(" FINANCIA.FINANCIANAME,  ");
			sql.append(" FINANCIA.SHORTNAME,  ");
			sql.append(" FINANCIA.REALNAMEPY,  ");
			sql.append(" TMS_USER.FLAG as STATUS  ");// 与user表关联取出帐号状态
			sql.append("FROM ");
			sql.append(TmsTableNames.TMS_FINANCIA);
			sql.append(" AS FINANCIA ");
			sql.append(" LEFT JOIN ");
			sql.append(TmsTableNames.TMS_USER);
			sql.append(" AS TMS_USER ");
			sql.append(" ON TMS_USER.USERID = FINANCIA.USERID ");
			sql.append(" WHERE 1=1");
			sql.append(" AND TMS_USER.FLAG = '"+status+"'");
			sql.append(" AND FINANCIA.REALNAMEPY LIKE '%"+realNamePy+"%'");
			sql.append(" ORDER BY FINANCIA.REALNAMEPY ASC");
			sql.append(" LIMIT "+limit);
			// 排序方法
			ps = conn.prepareStatement(sql.toString());
			rs = ps.executeQuery();

			while (rs.next()) {
				TmsFinanciaDto dto = new TmsFinanciaDto();
				dto.setFinanciaId(rs.getString("FINANCIAID"));
				dto.setFinanciaName(rs.getString("FINANCIANAME"));
				dto.setShortName(rs.getString("SHORTNAME"));
				dto.setRealNamePy(rs.getString("REALNAMEPY"));
				list.add(dto);
			}
		} catch (SQLException e) {
			logger.error("TmsFinanciaDaoImp.find error", e);
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}
		return list;
	}
	
}
